﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class VeDAO : DataProvider
    {
        public ArrayList GetVe()
        {
            connect();
            string sql = "select * from Ve";
            adapter = new SqlDataAdapter(sql, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            ArrayList arr = ConvertDataSetToArrayList(dataset);
            disconnect();
            return arr;
        }
        public ArrayList Get1Ve(DateTime ngay, int buoi)
        {
            connect();
            string temp = String.Format("{0:MM/dd/yyyy}", ngay);
            string sql = "SELECT * FROM dbo.Ve WHERE ngay = '" + temp +
                        "' and buoi = " + buoi +"";
            adapter = new SqlDataAdapter(sql, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            ArrayList arr = ConvertDataSetToArrayList(dataset);
            disconnect();
            return arr;
        }
        public int GetSoLuong(DateTime ngay, int buoi)
        {
            connect();
            string temp = String.Format("{0:MM/dd/yyyy}", ngay);
            string sql = "select so_luong - da_ban from Ve where ngay = '" + temp + "' and buoi = " + buoi;

            SqlCommand cmd = new SqlCommand(sql, connection);
            SqlDataReader reader = cmd.ExecuteReader();
            int res = 0;
            while (reader.Read())
                res = reader.GetInt32(0);

            disconnect();
            return res;
        }
        public int BanVe(DateTime ngay, int buoi, bool loaive, int sl, int dongia)
        {
            
            string a = loaive.ToString();
            string temp = String.Format("{0:MM/dd/yyyy}", ngay);
            try
            {
                if (Get1Ve(ngay, buoi).Count==0)
                {
                    connect();
                    string sql = "insert into Ve(loai_ve, ngay, buoi, da_ban, don_gia) values('" + loaive.ToString() + "', '" + temp + "', " + buoi.ToString() + ", " + sl.ToString() +","+dongia.ToString()+")";
                    executeNonQuery(sql);
                    disconnect();
                }
                else
                {
                    connect();
                    string sql = "UPDATE dbo.Ve SET da_ban = (SELECT da_ban + " + sl + " FROM dbo.Ve WHERE ngay = '" + temp +
                        "' and buoi = " + buoi + " and loai_ve='" + loaive + "') WHERE ngay = '" + temp + "' and buoi = " + buoi + " and loai_ve='" + loaive + "'";
                    executeNonQuery(sql);
                    disconnect();
                }
                
            }
            catch { return 0; }
            return 1;
        }
    }
}
